﻿using System;
using System.Collections.Generic;
using System.Text;
using Cashier_DAO.db;
using System.Data;
using System.Data.Common;
using Cashier_Model;
using System.Collections;

namespace Cashier_DAO
{
    public class ReportDAO : DAOFactory
    {
        #region 销售出货统计

        /// <summary>
        /// 统计销售出货报表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable ShipmentReport_SQLite(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(s.shipment_total) as result,sum(s.goods_total) as goods_total,strftime('%Y',s.shipment_date) as years,");
                sb.Append("strftime('%m',s.shipment_date) as months,strftime('%d',s.shipment_date) as days");
                sb.Append(" from bill_shipment s where s.id>0");
                Hashtable paramtable = new Hashtable();
                if (!String.IsNullOrEmpty(report.MemberNo))
                {
                    sb.Append(" and s.member_no=@memberno");
                    paramtable.Add("memberno", report.MemberNo);
                }
                if (!String.IsNullOrEmpty(report.Username))
                {
                    sb.Append(" and s.admin_username=@username");
                    paramtable.Add("username", report.Username);
                }
                if (report.Years > 0)
                {
                    sb.Append(" and years=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and months=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and days=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and s.shipment_date>date('now', '-").Append(report.LastLimtYears).Append(" year')");
                }
                if (!String.IsNullOrEmpty(report.Groupfield))
                {
                    sb.Append(" group by ").Append(report.Groupfield);
                }
                if (!String.IsNullOrEmpty(report.Orderfield))
                {
                    sb.Append(" order by ").Append(report.Orderfield).Append(" asc");
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                Console.WriteLine(cmd.CommandText);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_shipment");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 统计销售出货报表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable ShipmentReport_MySQL(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(s.shipment_total) as result,sum(s.goods_total) as goods_total,year(s.shipment_date) as years,");
                sb.Append("month(s.shipment_date) as months,day(s.shipment_date) as days");
                sb.Append(" from bill_shipment s where s.id>0");
                Hashtable paramtable = new Hashtable();
                if (!String.IsNullOrEmpty(report.MemberNo))
                {
                    sb.Append(" and s.member_no=@memberno");
                    paramtable.Add("memberno", report.MemberNo);
                }
                if (!String.IsNullOrEmpty(report.Username))
                {
                    sb.Append(" and s.admin_username=@username");
                    paramtable.Add("username", report.Username);
                }
                if (report.Years > 0)
                {
                    sb.Append(" and year(s.shipment_date)=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and month(s.shipment_date)=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and day(s.shipment_date)=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and s.shipment_date>date_add(now(), interval -").Append(report.LastLimtYears).Append(" year)");
                }
                if (!String.IsNullOrEmpty(report.Groupfield))
                {
                    sb.Append(" group by ").Append(report.Groupfield);
                }
                if (!String.IsNullOrEmpty(report.Orderfield))
                {
                    sb.Append(" order by ").Append(report.Orderfield).Append(" asc");
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                Console.WriteLine(cmd.CommandText);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_shipment");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 返回所有销售出货数据所在的年份
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public DataTable GetShipmentReportAllYears_SQLite(DbConnection conn)
        {
            DataTable dt = null;
            try
            {
                String sql = String.Empty;
                sql = "select strftime('%Y',shipment_date) as years from bill_shipment group by years order by years desc";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("years");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 返回所有销售出货数据所在的年份
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public DataTable GetShipmentReportAllYears_MySQL(DbConnection conn)
        {
            DataTable dt = null;
            try
            {
                String sql = "select year(shipment_date) as years from bill_shipment group by years order by years desc";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("years");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion

        #region 销售退货

        /// <summary>
        /// 统计销售退货报表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable ReshipmentReport_SQLite(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(reshipment_total) as result,strftime('%Y',reshipment_date) as years,");
                sb.Append("strftime('%m',reshipment_date) as months,strftime('%d',reshipment_date) as days from bill_reshipment where id>0");
                Hashtable paramtable = new Hashtable();
                if (!String.IsNullOrEmpty(report.MemberNo))
                {
                    sb.Append(" and member_no=@memberno");
                    paramtable.Add("memberno", report.MemberNo);
                }
                if (report.Years > 0)
                {
                    sb.Append(" and years=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and months=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and days=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and reshipment_date>date('now', '-").Append(report.LastLimtYears).Append(" year')");
                }
                if (!String.IsNullOrEmpty(report.Groupfield))
                {
                    sb.Append(" group by ").Append(report.Groupfield);
                }
                if (!String.IsNullOrEmpty(report.Orderfield))
                {
                    sb.Append(" order by ").Append(report.Orderfield).Append(" asc");
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_reshipment");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 统计销售退货报表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable ReshipmentReport_MySQL(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(reshipment_total) as result,year(reshipment_date) as years,");
                sb.Append("month(reshipment_date) as months,day(reshipment_date) as days from bill_reshipment where id>0");
                Hashtable paramtable = new Hashtable();
                if (!String.IsNullOrEmpty(report.MemberNo))
                {
                    sb.Append(" and member_no=@memberno");
                    paramtable.Add("memberno", report.MemberNo);
                }
                if (report.Years > 0)
                {
                    sb.Append(" and year(reshipment_date)=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and month(reshipment_date)=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and day(reshipment_date)=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (!String.IsNullOrEmpty(report.Groupfield))
                {
                    sb.Append(" group by ").Append(report.Groupfield);
                }
                if (!String.IsNullOrEmpty(report.Orderfield))
                {
                    sb.Append(" order by ").Append(report.Orderfield).Append(" asc");
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_reshipment");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion

        #region 采购进货

        /// <summary>
        /// 采购进货报表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable PurchaseReport_SQLite(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(p.purchase_total) as result,sum(p.goods_total) as goods_total,strftime('%Y',p.purchase_date) as years,");
                sb.Append("strftime('%m',p.purchase_date) as months,strftime('%d',p.purchase_date) as days,sum(r.count_total) as retotal");
                sb.Append(" from bill_purchase p left join bill_repurchase r on strftime('%Y',p.purchase_date)=strftime('%Y',r.repurchase_date)");
                sb.Append(" and strftime('%m',p.purchase_date)=strftime('%m',r.repurchase_date)");
                if (report.Days > 0)
                    sb.Append(" and strftime('%d',p.purchase_date)=strftime('%d',r.repurchase_date)");
                sb.Append(" where p.id>0");
                Hashtable paramtable = new Hashtable();
                if (report.Years > 0)
                {
                    sb.Append(" and years=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and months=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and days=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and p.purchase_date>date('now','-").Append(report.LastLimtYears).Append(" year')");
                }
                if (!String.IsNullOrEmpty(report.Groupfield))
                {
                    sb.Append(" group by ").Append(report.Groupfield);
                }
                if (!String.IsNullOrEmpty(report.Orderfield))
                {
                    sb.Append(" order by ").Append(report.Orderfield).Append(" asc");
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_purchase");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 采购进货报表
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable PurchaseReport_MySQL(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(p.purchase_total) as result,sum(p.goods_total) as goods_total,year(p.purchase_date) as years,");
                sb.Append("month(p.purchase_date) as months,day(p.purchase_date) as days,sum(r.count_total) as retotal");
                sb.Append(" from bill_purchase p left join bill_repurchase r on year(p.purchase_date)=year(r.repurchase_date)");
                sb.Append(" and month(p.purchase_date)=month(r.repurchase_date)");
                if (report.Days > 0)
                    sb.Append(" and day(p.purchase_date)=day(r.repurchase_date)");
                sb.Append(" where p.id>0");
                Hashtable paramtable = new Hashtable();
                if (report.Years > 0)
                {
                    sb.Append(" and year(p.purchase_date)=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and month(p.purchase_date)=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and day(p.purchase_date)=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and p.purchase_date>date_add(now(),interval -").Append(report.LastLimtYears).Append(" year)");
                }
                if (!String.IsNullOrEmpty(report.Groupfield))
                {
                    sb.Append(" group by ").Append(report.Groupfield);
                }
                if (!String.IsNullOrEmpty(report.Orderfield))
                {
                    sb.Append(" order by ").Append(report.Orderfield).Append(" asc");
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_purchase");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 返回所有采购进货数据所在的年份
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public DataTable GetPurchaseReportAllYears_SQLite(DbConnection conn)
        {
            DataTable dt = null;
            try
            {
                String sql = "select strftime('%Y',purchase_date) as years from bill_purchase group by years order by years desc";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("years");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 返回所有采购进货数据所在的年份
        /// </summary>
        /// <param name="conn"></param>
        /// <returns></returns>
        public DataTable GetPurchaseReportAllYears_MySQL(DbConnection conn)
        {
            DataTable dt = null;
            try
            {
                String sql = "select year(purchase_date) as years from bill_purchase group by years order by years desc";
                DbCommand cmd = CommandFactory.CreateCommand(sql, conn);
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                dt = new DataTable("years");
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion

        #region 会员消费统计

        /// <summary>
        /// 会员消费统计 SQLite
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable MemberTakeReport_SQLite(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(s.shipment_total) as result,sum(s.goods_total) as goods_total,strftime('%Y',s.shipment_date) as years,");
                sb.Append("strftime('%m',s.shipment_date) as months,strftime('%d',s.shipment_date) as days,sum(r.reshipment_total) as retotal,s.member_no");
                sb.Append(" from bill_shipment s left join bill_reshipment r on s.member_no=r.member_no");
                if (report.Years > 0)
                    sb.Append(" and strftime('%Y',s.shipment_date)=strftime('%Y',r.reshipment_date)");
                if (report.Months > 0)
                    sb.Append(" and strftime('%m',s.shipment_date)=strftime('%m',r.reshipment_date)");
                if (report.Days > 0)
                    sb.Append(" and strftime('%d',s.shipment_date)=strftime('%d',r.reshipment_date)");
                sb.Append(" where s.member_no!=''");
                Hashtable paramtable = new Hashtable();
                if (!String.IsNullOrEmpty(report.MemberNo))
                {
                    sb.Append(" and s.member_no=@memberno");
                    paramtable.Add("memberno", report.MemberNo);
                }
                if (report.Years > 0)
                {
                    sb.Append(" and years=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and months=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and days=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and s.shipment_date>date('now', '-").Append(report.LastLimtYears).Append(" year')");
                }
                sb.Append(" group by s.").Append(report.Groupfield).Append(" order by ").Append(report.Orderfield).Append(" asc");

                if (report.PageSize > 0)
                {
                    sb.Append(" limit @offset,@pagesize");
                    paramtable.Add("offset", (report.PageIndex - 1) * report.PageSize);
                    paramtable.Add("pagesize", report.PageSize);
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                Console.WriteLine(cmd.CommandText);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_shipment");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// 用户消费统计 MySQL
        /// </summary>
        /// <param name="conn"></param>
        /// <param name="report"></param>
        /// <returns></returns>
        public DataTable MemberTakeReport_MySQL(DbConnection conn, ReportParams report)
        {
            DataTable dt = null;
            try
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("select sum(s.shipment_total) as result,sum(s.goods_total) as goods_total,year(s.shipment_date) as years,");
                sb.Append("month(s.shipment_date) as months,day(s.shipment_date) as days,sum(r.reshipment_total) as retotal,s.member_no");
                sb.Append(" from bill_shipment s left join bill_reshipment r on s.member_no=r.member_no");
                if (report.Years > 0)
                    sb.Append(" and year(s.shipment_date)=year(r.reshipment_date)");
                if (report.Months > 0)
                    sb.Append(" and month(s.shipment_date)=month(r.reshipment_date)");
                if (report.Days > 0)
                    sb.Append(" and day(s.shipment_date)=day(r.reshipment_date)");
                sb.Append(" where s.member_no!=''");
                Hashtable paramtable = new Hashtable();
                if (!String.IsNullOrEmpty(report.MemberNo))
                {
                    sb.Append(" and s.member_no=@memberno");
                    paramtable.Add("memberno", report.MemberNo);
                }
                if (report.Years > 0)
                {
                    sb.Append(" and year(s.shipment_date)=@years");
                    paramtable.Add("years", report.Years.ToString());
                }
                if (report.Months > 0)
                {
                    sb.Append(" and month(s.shipment_date)=@months");
                    paramtable.Add("months", report.Months < 10 ? "0" + report.Months : report.Months.ToString());
                }
                if (report.Days > 0)
                {
                    sb.Append(" and day(s.shipment_date)=@days");
                    paramtable.Add("days", report.Days < 10 ? "0" + report.Days : report.Days.ToString());
                }
                if (report.LastLimtYears > 0)
                {
                    sb.Append(" and s.shipment_date>date_add(now(), interval -").Append(report.LastLimtYears).Append(" year)");
                }
                sb.Append(" group by ").Append(report.Groupfield).Append(" order by ").Append(report.Orderfield).Append(" desc");

                if (report.PageSize > 0)
                {
                    sb.Append(" limit @offset,@pagesize");
                    paramtable.Add("offset", (report.PageIndex - 1) * report.PageSize);
                    paramtable.Add("pagesize", report.PageSize);
                }

                DbCommand cmd = CommandFactory.CreateCommand(sb.ToString(), conn);
                Console.WriteLine(cmd.CommandText);
                this.AddCommandParamters(cmd, paramtable);
                dt = new DataTable("bill_shipment");
                DbDataAdapter da = DataAdapterFactory.CreateDataAdapter(cmd);
                da.Fill(dt);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        #endregion
    }
}